set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;

insert overwrite table jms_dm.dm_send_warning_detail_dt
select
      reg.waybill_no                     as  waybill_no                     --运单号
     ,bagg.package_code                  as  package_code                   --所属包号
     ,reg.scan_time                      as  reg_scan_time                  --有发未到登记时间
     ,reg.registr_scan_site_code         as  registr_scan_site_code         --登记网点编码
     ,reg.registr_scan_site_name         as  registr_scan_site_name         --登记网点名称
     ,bagg.scan_site_code                as  bagg_scan_site_code            --登记环节/建包网点编码
     ,bagg.scan_site                     as  bagg_scan_site_name            --登记环节/建包网点名称
     ,bagg.scan_time                     as  bagg_scan_time                 --建包时间
     ,dim.code                           as  first_center_code              --始发转运中心编码
     ,dim.name                           as  first_center_name              --始发转运中心名称
     ,dim.agent_code                     as  first_center_agent_code        --始发转运中心编码
     ,dim.agent_name                     as  first_center_agent_name        --始发转运中心名称
     ,wide.first_center_arr_shipment_no  as  first_center_arr_shipment_no   --始发转运中心任务号
     ,wide.first_center_lines_name       as  first_center_lines_name        --始发中心线路名称
     ,wide.first_center_arr_time         as  first_center_arrival_time      --始发转运中心实际发车时间
     ,wide.first_center_send_time        as  first_center_send_time         --始发转运中心发件时间
     ,case when first.center_code = wide.end_center_code then wide.end_center_actual_arr_time end as end_center_actual_arr_time --末端中心到车时间
     ,case when first.center_code = wide.end_center_code then wide.end_center_arr_time end as end_center_arr_time --末端中心到件时间
     ,wide.order_source_code as order_source_code --订单来源编码
     ,wide.order_source_name as order_source_name --订单来源名称
     ,case when first.center_code = wide.end_center_code then 1 else 0 end as if_arr --是否有发有到
     ,to_date(reg.scan_time) as dt
from(
    select *
    from (
        select
              waybill_no
             ,scan_time                 as scan_time                --登记时间
             ,package_number            as package_code             --包号
             ,proble_type_subject_code  as proble_type_subject_code --一级类型名称
             ,proble_type_subject_name  as proble_type_subject_name --一级类型编码
             ,registration_network_code as registr_scan_site_code   --登记网点编码
             ,registration_network_name as registr_scan_site_name   --登记网点名称
             ,row_number() over(partition by waybill_no,registration_network_code order by scan_time desc) as rn
        from jms_dwd.dwd_sqs_registration_problem_piece_base_dt
        where dt between date_sub('{{ execution_date | cst_ds }}',7) and '{{ execution_date | cst_ds }}'
          and to_date(scan_time) between date_sub('{{ execution_date | cst_ds }}',7) and '{{ execution_date | cst_ds }}'
          and proble_type_subject_code = '29'  --有发未到
    ) a where a.rn = 1
) reg
left join (
    select
          waybill_no                                           as waybill_no
         ,first_code                                           as first_code                      --一段码
         ,first_center_network_code                            as first_center_code               --始发转运中心编码
         ,first_center_loading_send_vehicleline_name           as first_center_lines_name
         ,first_center_loading_send_shipment_no                as first_center_arr_shipment_no    --始发中心任务号
         ,first_center_loading_later_time                      as first_center_arr_time           --始发中心装车发车时间
         ,first_center_send_later_time                         as first_center_send_time          --始发中心最晚发件时间
         ,end_center_network_code                              as end_center_code                 --末端转运中心编码
         ,end_center_unloading_arrival_actual_arrival_time     as end_center_actual_arr_time      --末端中心最早到车时间
         ,end_center_arrival_early_time                        as end_center_arr_time             --末端中心最早到件时间
         ,ordersource_code                                     as order_source_code               --订单来源编码
         ,ordersource_name                                     as order_source_name               --订单来源名称
    from jms_dwd.dwd_wide_unsign_summary_waybill_dt
    where dt between date_sub('{{ execution_date | cst_ds }}',14) and '{{ execution_date | cst_ds }}'
) wide on reg.waybill_no = wide.waybill_no
left join(
    select *
    from(
        select
              waybill_no
             ,input_time
             ,scan_site
             ,scan_site_code
             ,scan_time
             ,package_code
             ,row_number() over(partition by waybill_no order by scan_time desc) as rn  --去重
        from jms_dwd.dwd_tab_barscan_bagging_base_dt
        where dt between date_sub('{{ execution_date | cst_ds }}',7) and '{{ execution_date | cst_ds }}'
    ) a where a.rn = 1
) bagg on reg.waybill_no = bagg.waybill_no
left join (
    select
          code
         ,name
         ,agent_code
         ,agent_name
    from jms_dim.dim_network_whole_massage
) dim on wide.first_center_code = dim.code
left join (
    select code,center_name,center_code
    from jms_dim.dim_lmdm_sys_first_code
    where is_enable=1 and is_delete = 1
    group by code,center_name,center_code
) first on wide.first_code = first.code
left join (
    select
          code
         ,name
         ,network_type
    from jms_dim.dim_network_whole_massage
) network on reg.registr_scan_site_code = network.code
where network.network_type = '4'
distribute by dt,pmod(hash(rand()),5);